package servicios.biziGasteiz;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import connection.BDConnection;

/**
 * Servlet implementation class GetRestaurants
 */
public class GetRestaurants extends HttpServlet {
	private static final long serialVersionUID = 1L;
    private String CONTENT_TYPE = "text/json";
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GetRestaurants() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		this.processRequest(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		this.processRequest(request, response);
	}
	
	private void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String requestType = (String)request.getParameter("request");
		try{
			if(requestType.compareToIgnoreCase("GETRESTAURANTSTABLE") == 0){
				this.getRestaurantsTable(request, response);
			}
			else if(requestType.compareToIgnoreCase("GETRESTAURANT") == 0){
				this.getRestaurant(request, response);
			}
			else if(requestType.compareToIgnoreCase("GETMORERESTAURANTS") == 0){
				this.getMoreRestaurants(request, response);
			}
			else if(requestType.compareToIgnoreCase("GETMORERESTAURANTSBYFAMILY") == 0){
				this.getMoreRestaurantsByFamily(request, response);
			}
		}
		catch(Exception oo){oo.printStackTrace();}
	}

	private void getMoreRestaurants(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String code = request.getParameter("code");
			
			String sqlStr = "SELECT nombre,descripcion_corta,tipo,foto_principal," +
					"ST_ASTEXT(localizacion) as localizacion,horario,family," +
					"restaurantid,descripcion,calle,descripcion_normal,telefono,email  FROM restaurantes where restaurantid<>"+code;
			ResultSet rs = bdCon.executeQuery(sqlStr);
			
			JSONArray resJsonArray = new JSONArray();

			while(rs.next()){
				JSONObject itemJson = new JSONObject();
				itemJson.put("nombre", rs.getString("nombre"));
				itemJson.put("descripcion_corta", rs.getString("descripcion_corta"));
				itemJson.put("tipo", rs.getString("tipo"));
				itemJson.put("foto_principal", rs.getString("foto_principal"));
				itemJson.put("localizacion", rs.getString("localizacion"));
				itemJson.put("horario", rs.getString("horario"));
				itemJson.put("restaurantid", rs.getInt("restaurantid"));
				itemJson.put("descripcion", rs.getString("descripcion"));
				itemJson.put("calle", rs.getString("calle"));
				itemJson.put("descripcion_normal", rs.getString("descripcion_normal"));
				itemJson.put("telefono", rs.getString("telefono"));
				itemJson.put("email", rs.getString("email"));
				itemJson.put("family", rs.getString("family"));
				JSONArray menuJsonArray = new JSONArray();
				String sqlMenu = "SELECT * FROM MENU WHERE restauranteid='"+rs.getInt("restaurantid")+"'";
				ResultSet rs1 = bdCon.executeQuery(sqlMenu);
				while(rs1.next()){
					JSONObject menuJson = new JSONObject();
					menuJson.put("restauranteid", rs1.getString("restauranteid"));
					menuJson.put("precio", rs1.getDouble("precio"));
					menuJson.put("descripcion", rs1.getString("descripcion"));
					menuJson.put("menuid", rs1.getInt("menuid"));
					menuJson.put("foto", rs1.getString("foto"));
					menuJson.put("nombre", rs1.getString("nombre"));
					menuJson.put("tipo", rs1.getString("tipo"));
					menuJson.put("sub_tipo", rs1.getString("sub_tipo"));
					menuJsonArray.put(menuJson);
				}
				itemJson.put("menus", menuJsonArray);
				resJsonArray.put(itemJson);
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", resJsonArray);
			
			print.write(resJson.toString());
			bdCon.closeConnection();
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			oo.printStackTrace();
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
			
		}
	}
	
	private void getMoreRestaurantsByFamily(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String code = request.getParameter("code");
			String family = request.getParameter("family");
			
			String sqlStr = "SELECT nombre,descripcion_corta,tipo,foto_principal," +
					"ST_ASTEXT(localizacion) as localizacion,horario,family," +
					"restaurantid,descripcion,calle,descripcion_normal,telefono,email  FROM restaurantes where family='"+family+"' AND restaurantid<>"+code;
			System.out.println(sqlStr);
			ResultSet rs = bdCon.executeQuery(sqlStr);
			
			JSONArray resJsonArray = new JSONArray();

			while(rs.next()){
				JSONObject itemJson = new JSONObject();
				itemJson.put("nombre", rs.getString("nombre"));
				itemJson.put("descripcion_corta", rs.getString("descripcion_corta"));
				itemJson.put("tipo", rs.getString("tipo"));
				itemJson.put("foto_principal", rs.getString("foto_principal"));
				itemJson.put("localizacion", rs.getString("localizacion"));
				itemJson.put("horario", rs.getString("horario"));
				itemJson.put("restaurantid", rs.getInt("restaurantid"));
				itemJson.put("descripcion", rs.getString("descripcion"));
				itemJson.put("calle", rs.getString("calle"));
				itemJson.put("descripcion_normal", rs.getString("descripcion_normal"));
				itemJson.put("telefono", rs.getString("telefono"));
				itemJson.put("email", rs.getString("email"));
				itemJson.put("family", rs.getString("family"));
				JSONArray menuJsonArray = new JSONArray();
				String sqlMenu = "SELECT * FROM MENU WHERE restauranteid='"+rs.getInt("restaurantid")+"'";
				ResultSet rs1 = bdCon.executeQuery(sqlMenu);
				while(rs1.next()){
					JSONObject menuJson = new JSONObject();
					menuJson.put("restauranteid", rs1.getString("restauranteid"));
					menuJson.put("precio", rs1.getDouble("precio"));
					menuJson.put("descripcion", rs1.getString("descripcion"));
					menuJson.put("menuid", rs1.getInt("menuid"));
					menuJson.put("foto", rs1.getString("foto"));
					menuJson.put("nombre", rs1.getString("nombre"));
					menuJson.put("tipo", rs1.getString("tipo"));
					menuJson.put("sub_tipo", rs1.getString("sub_tipo"));
					menuJsonArray.put(menuJson);
				}
				itemJson.put("menus", menuJsonArray);
				resJsonArray.put(itemJson);
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", resJsonArray);
			
			print.write(resJson.toString());
			bdCon.closeConnection();
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			oo.printStackTrace();
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
			
		}
	}
	
	private void getRestaurantsTable(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String table = request.getParameter("table");
			
			String sqlStr = "SELECT nombre,descripcion_corta,tipo,foto_principal," +
					"ST_ASTEXT(localizacion) as localizacion,horario,family," +
					"restaurantid,descripcion,calle,descripcion_normal,telefono,email,destacado  FROM "+table;
			ResultSet rs = bdCon.executeQuery(sqlStr);
			
			JSONArray resJsonArray = new JSONArray();

			while(rs.next()){
				JSONObject itemJson = new JSONObject();
				itemJson.put("nombre", rs.getString("nombre"));
				itemJson.put("descripcion_corta", rs.getString("descripcion_corta"));
				itemJson.put("tipo", rs.getString("tipo"));
				itemJson.put("foto_principal", rs.getString("foto_principal"));
				itemJson.put("localizacion", rs.getString("localizacion"));
				itemJson.put("horario", rs.getString("horario"));
				itemJson.put("restaurantid", rs.getInt("restaurantid"));
				itemJson.put("descripcion", rs.getString("descripcion"));
				itemJson.put("calle", rs.getString("calle"));
				itemJson.put("descripcion_normal", rs.getString("descripcion_normal"));
				itemJson.put("telefono", rs.getString("telefono"));
				itemJson.put("email", rs.getString("email"));
				itemJson.put("family", rs.getString("family"));
				itemJson.put("destacado", rs.getBoolean("destacado"));
				JSONArray menuJsonArray = new JSONArray();
				String sqlMenu = "SELECT * FROM MENU WHERE restauranteid='"+rs.getInt("restaurantid")+"'";
				ResultSet rs1 = bdCon.executeQuery(sqlMenu);
				while(rs1.next()){
					JSONObject menuJson = new JSONObject();
					menuJson.put("restauranteid", rs1.getString("restauranteid"));
					menuJson.put("precio", rs1.getDouble("precio"));
					menuJson.put("descripcion", rs1.getString("descripcion"));
					menuJson.put("menuid", rs1.getInt("menuid"));
					menuJson.put("foto", rs1.getString("foto"));
					menuJson.put("nombre", rs1.getString("nombre"));
					menuJson.put("tipo", rs1.getString("tipo"));
					menuJson.put("sub_tipo", rs1.getString("sub_tipo"));
					menuJsonArray.put(menuJson);
				}
				itemJson.put("menus", menuJsonArray);
				resJsonArray.put(itemJson);
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", resJsonArray);
			
			print.write(resJson.toString());
			bdCon.closeConnection();
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			oo.printStackTrace();
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
			
		}
	}
	
	private void getRestaurant(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String code = request.getParameter("code");
			
			String sqlStr = "SELECT nombre,descripcion_corta,tipo,foto_principal," +
					"ST_ASTEXT(localizacion) as localizacion,horario,family," +
					"restaurantid,descripcion,calle,descripcion_normal,telefono,email  FROM restaurantes where restaurantid="+code;
			ResultSet rs = bdCon.executeQuery(sqlStr);
			
			JSONObject itemJson = new JSONObject();

			if(rs.next()){
				
				itemJson.put("nombre", rs.getString("nombre"));
				itemJson.put("descripcion_corta", rs.getString("descripcion_corta"));
				itemJson.put("tipo", rs.getString("tipo"));
				itemJson.put("foto_principal", rs.getString("foto_principal"));
				itemJson.put("localizacion", rs.getString("localizacion"));
				itemJson.put("horario", rs.getString("horario"));
				itemJson.put("restaurantid", rs.getInt("restaurantid"));
				itemJson.put("descripcion", rs.getString("descripcion"));
				itemJson.put("calle", rs.getString("calle"));
				itemJson.put("descripcion_normal", rs.getString("descripcion_normal"));
				itemJson.put("telefono", rs.getString("telefono"));
				itemJson.put("email", rs.getString("email"));
				itemJson.put("family", rs.getString("family"));
				JSONArray menuJsonArray = new JSONArray();
				String sqlMenu = "SELECT * FROM MENU WHERE restauranteid='"+rs.getInt("restaurantid")+"'";
				ResultSet rs1 = bdCon.executeQuery(sqlMenu);
				while(rs1.next()){
					JSONObject menuJson = new JSONObject();
					menuJson.put("restauranteid", rs1.getString("restauranteid"));
					menuJson.put("precio", rs1.getDouble("precio"));
					menuJson.put("descripcion", rs1.getString("descripcion"));
					menuJson.put("menuid", rs1.getInt("menuid"));
					menuJson.put("foto", rs1.getString("foto"));
					menuJson.put("nombre", rs1.getString("nombre"));
					menuJson.put("tipo", rs1.getString("tipo"));
					menuJson.put("sub_tipo", rs1.getString("sub_tipo"));
					menuJsonArray.put(menuJson);
				}
				itemJson.put("menus", menuJsonArray);
				ResultSet rs2 = bdCon.executeQuery("SELECT * FROM restaurantes_fotos WHERE restauranteid='"+code+"'");
				JSONArray fotosArray = new JSONArray();
				while(rs2.next()){
					fotosArray.put(rs2.getString("url"));
				}
				itemJson.put("images", fotosArray);
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", itemJson);
			
			print.write(resJson.toString());
			bdCon.closeConnection();
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			oo.printStackTrace();
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
			
		}
	}
}
